import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Tp5 - Base de Données
 * 
 * @author David Bernard
 * @author Maxime Lemieux
 */
public class Tp5
{
	private static final String OK = "OK.....";
	private static final String FAIL = "FAIL...";
	private static final String SEPA = "##################################\n";

	private static final int NO_EMPL_TEST = 101;
	private static final int SALAIRE_TEST = 50000;

	private OracleJava conn = null;

	public Tp5() throws Exception
	{
		final String METHODE = "Connection";
		System.out.println("### DEBUT " + METHODE + " ###");

		try
		{
			this.conn = new OracleJava("a11_davi_be", "1011169",
					"10.209.245.31", "1521", "bdlupin");

			System.out.println(Tp5.OK + METHODE);
		}
		catch (Exception e)
		{
			System.out.println(Tp5.FAIL + METHODE);
			throw e;
		}
		finally
		{
			System.out.println(Tp5.SEPA);
		}
	}

	public void destructionTable()
	{
		final String METHODE = "destructionTable";
		System.out.println("### DEBUT " + METHODE + " ###");

		String requete = "drop table statdepemp";

		System.out.println(requete);
		try
		{
			this.conn.executerUpdate("drop table statdepemp");
			System.out.println(Tp5.OK + "destructionTable");
		}
		catch (SQLException e)
		{
			System.out.println(Tp5.FAIL + "destructionTable");
		}

		System.out.println(Tp5.SEPA);
	}

	public void creationTable()
	{
		final String METHODE = "creationTable";
		System.out.println("### DEBUT " + METHODE + " ###");

		String requete = "CREATE TABLE statDepEmp(";
		requete += " code_depa char(1),";
		requete += "nbre_empl number(2),";
		requete += "tot_salaire number(9,2),";
		requete += "CONSTRAINT PK_DEPA PRIMARY KEY(code_DEPA))";

		System.out.println(requete);

		try
		{
			this.conn.executerUpdate(requete);
			System.out.println(Tp5.OK + METHODE);
		}
		catch (SQLException e)
		{
			System.out.println(Tp5.FAIL + METHODE);
		}

		System.out.println(Tp5.SEPA);
	}

	public void iniStat()
	{
		final String METHODE = "iniStat";
		String requete = "call PACK_EMP.P_INI_STAT()";
		System.out.println("### DEBUT " + METHODE + " ###");

		System.out.println(requete);
		try
		{
			this.conn.executer(requete);
			System.out.println(Tp5.OK + METHODE);
		}
		catch (SQLException e)
		{
			System.out.println(Tp5.FAIL + METHODE);
		}

		System.out.println(Tp5.SEPA);
	}

	public void afficherStatDepEmpl()
	{
		final String METHODE = "afficherStatDepEmpl";
		ResultSet res = null;
		String requete = "SELECT * FROM statdepemp";
		String out = "";

		System.out.println("### DEBUT " + METHODE + " ###");

		System.out.println(requete);

		try
		{
			res = this.conn.executerRequete(requete);

			System.out.println("code_depa;nbre_empl;tot_salaire");

			while (res.next())
			{
				out = "";

				out += res.getString("code_depa");
				out += ";";

				out += res.getInt("nbre_empl");
				out += ";";

				out += res.getFloat("tot_salaire");

				System.out.println(out);
			}
			System.out.println(Tp5.OK + METHODE);
		}
		catch (SQLException e)
		{
			System.out.println(Tp5.FAIL + METHODE);
		}

		System.out.println(Tp5.SEPA);
	}

	public void insertNouvEmpl(int noEmpl, String nom, String prenom,
			float salaire, String codeDepa, String codeProf)
	{
		final String METHODE = "insertNouvEmpl";
		System.out.println("### DEBUT " + METHODE + " ###");

		String requete = "INSERT INTO EMPLOYE_PL VALUES(";
		requete += noEmpl + ",";
		requete += "'" + nom + "',";
		requete += "'" + prenom + "',";
		requete += salaire + ",";
		requete += "'" + codeDepa + "',";
		requete += "'" + codeProf + "'";
		requete += ")";

		System.out.println(requete);

		try
		{
			this.conn.executerUpdate(requete);
			System.out.println(Tp5.OK + "insertNouvEmpl");
		}
		catch (SQLException e)
		{
			System.out.println(Tp5.FAIL + "insertNouvEmpl");
			e.printStackTrace();
		}
		System.out.println(Tp5.SEPA);
	}

	public void deleteEmploye(int noEmpl)
	{
		final String METHODE = "deleteEmploye";
		System.out.println("### DEBUT " + METHODE + " ###");

		String requete = "delete from employe_pl where no_empl = " + noEmpl;
		System.out.println(requete);
		try
		{
			this.conn.executerUpdate(requete);
			System.out.println(Tp5.OK + " deleteEmploye");
		}
		catch (SQLException e)
		{
			System.out.println(Tp5.FAIL + " deleteEmploye");
		}
		System.out.println(Tp5.SEPA);
	}

	public void modifSalaire(int noEmpl, float pourcentAugm)
	{
		final String METHODE = "modifSalaire";
		System.out.println("### DEBUT " + METHODE + " ###");

		String requete = "";
		float salaire = 0;
		ResultSet rs = null;

		try
		{

			requete = "SELECT sala_empl FROM EMPLOYE_PL where ";
			requete += "no_empl = " + noEmpl;
			System.out.println(requete);

			rs = this.conn.executerRequete(requete);
			rs.next();
			salaire = rs.getFloat("sala_empl");
			System.out.println("ancien salaire = " + salaire);

			requete = "update employe_pl set sala_empl = PACK_EMP.nouvSalaire(";
			requete += salaire + ", " + pourcentAugm;
			requete += ")";

			System.out.println(requete);
			this.conn.executer(requete);

			requete = "SELECT sala_empl FROM EMPLOYE_PL where no_empl = "
					+ noEmpl;
			System.out.println(requete);
			rs = this.conn.executerRequete(requete);
			rs.next();
			salaire = rs.getFloat("sala_empl");
			System.out.println("nouveau salaire = " + salaire);

			System.out.println(Tp5.OK + METHODE);
		}
		catch (SQLException e)
		{
			System.out.println(Tp5.FAIL + METHODE);
		}

		System.out.println(Tp5.SEPA);
	}

	public void modifDeptEmpl(int noEmpl, String dept)
	{
		final String METHODE = "modifDeptEmpl";
		System.out.println("### DEBUT " + METHODE + " ###");

		String requete = "update employe_pl set code_dept = '" + dept
				+ "' where no_empl = " + noEmpl;

		System.out.println(requete);

		try
		{
			this.conn.executerUpdate(requete);
			System.out.println(Tp5.OK + METHODE);
		}
		catch (SQLException e)
		{
			System.out.println(Tp5.FAIL + METHODE);
		}

		System.out.println(Tp5.SEPA);
	}

	public void modifProfEmpl(int noEmpl, String prof)
	{
		final String METHODE = "modifProfEmpl";
		System.out.println("### DEBUT " + METHODE + " ###");

		String requete = "update employe_pl set type_prof = ";

		requete += "'" + prof + "'";
		requete += " where no_empl = " + noEmpl;

		System.out.println(requete);

		try
		{
			this.conn.executerUpdate(requete);
			System.out.println(Tp5.OK + METHODE);
		}
		catch (SQLException e)
		{
			System.out.println(Tp5.FAIL + METHODE);
		}

		System.out.println(Tp5.SEPA);
	}

	public static void main(String[] args)
	{
		Tp5 demo = null;

		System.out.println("DEMO TP5 - base de donnée");
		System.out.println("par David Bernard et Maxime Lemieux");
		System.out.println("Groupe 1");
		System.out.println();

		try
		{
			demo = new Tp5();

		}
		catch (Exception e)
		{
			e.printStackTrace();
		}

		if (demo != null)
		{
			demo.destructionTable();

			demo.creationTable();

			demo.iniStat();
			demo.afficherStatDepEmpl();

			demo.insertNouvEmpl(Tp5.NO_EMPL_TEST, "Bernard", "David",
					Tp5.SALAIRE_TEST, "M", "AD");
			demo.afficherStatDepEmpl();

			demo.modifSalaire(Tp5.NO_EMPL_TEST, 5);
			demo.afficherStatDepEmpl();

			demo.modifDeptEmpl(NO_EMPL_TEST, "G");
			demo.afficherStatDepEmpl();

			demo.modifProfEmpl(NO_EMPL_TEST, "SC");
			demo.afficherStatDepEmpl();

			demo.deleteEmploye(Tp5.NO_EMPL_TEST);
			demo.afficherStatDepEmpl();
		}

	}
}
